import pandas as pd
from fastapi import APIRouter, Request
from fastapi.templating import Jinja2Templates
from fastapi.responses import HTMLResponse

api_data = APIRouter()

# 设置模板目录
templates = Jinja2Templates(directory="templates")

# 加载并分析数据
df = pd.read_excel("./data/销售记录.xlsx")

# 分析 1：按客户统计总金额
total_by_customer = df.groupby('客户名称')['金额'].sum().reset_index().to_dict(orient='records')

# 分析 2：按省份统计订单数量
orders_by_province = df.groupby('客户省份')['订单编号'].count().reset_index().to_dict(orient='records')

# 分析 3：计算平均送货延迟时间
df['下单日期'] = pd.to_datetime(df['下单日期'])
df['实际送货日期'] = pd.to_datetime(df['实际送货日期'])
df['送货延迟天数'] = (df['实际送货日期'] - df['下单日期']).dt.days
average_delay = df['送货延迟天数'].mean()

@api_data.get("/", response_class=HTMLResponse)
async def read_index(request: Request):
    # 将数据传递给模板
    return templates.TemplateResponse("index.html", {
        "request": request,
        "total_by_customer": total_by_customer,
        "orders_by_province": orders_by_province,
        "average_delay": average_delay
    })

